﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MJM.SUD.EntidadesNegocio;
using MJM.CBD.Conn;
using System.Data;
using System.Data.SqlClient;

namespace MJM.SUD.AccesoDatos
{
    public class UbicacioAD
    {
        public List<UbicacionE> ListarDistritos(int Provincia,int Departamento)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_WEB_T_MAES_UBIG_ListaDistritos", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            List<UbicacionE> lst = new List<UbicacionE>();
            SqlParameter prmProvincia;
            SqlParameter prmDepartamento;
            try
            {
                prmProvincia = cmd.CreateParameter();
                prmProvincia.ParameterName = "@Provincia";
                prmProvincia.SqlDbType = SqlDbType.Int;
                prmProvincia.Value = Provincia;

                prmDepartamento = cmd.CreateParameter();
                prmDepartamento.ParameterName = "@Departamento";
                prmDepartamento.SqlDbType = SqlDbType.Int;
                prmDepartamento.Value = Departamento;
                cmd.Parameters.Add(prmDepartamento);
                cmd.Parameters.Add(prmProvincia);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                UbicacionE obj = new UbicacionE();
                while (dr.Read())
                {
                    obj = new UbicacionE();
                    obj.Descripcion = dr.GetString(dr.GetOrdinal("UBIG_chDESDIS"));
                    obj.IDUbicacion = dr.GetInt32(dr.GetOrdinal("UBIG_chCODDIS"));
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                conn.Dispose();
                cmd.Dispose();
            }
            return lst;
        }

        public List<UbicacionE> ListarProvincia(int Departamento)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_WEB_T_MAES_UBIG_ListaProvincias", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            SqlParameter prmDepartamento;
            List<UbicacionE> lst=new List<UbicacionE>();
            try
            {
                prmDepartamento = cmd.CreateParameter();
                prmDepartamento.SqlDbType = SqlDbType.Int;
                prmDepartamento.ParameterName = "@Departamento";
                prmDepartamento.Value = Departamento;

                cmd.Parameters.Add(prmDepartamento);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                UbicacionE obj = new UbicacionE();
                while (dr.Read())
                {
                    obj = new UbicacionE();
                    obj.Descripcion = dr.GetString(dr.GetOrdinal("UBIG_chDESPRV"));
                    obj.IDUbicacion = dr.GetInt32(dr.GetOrdinal("UBIG_chCODPRV"));
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                cmd.Dispose(); 
            }
            catch(Exception ex)
            {
                conn.Dispose();
                cmd.Dispose();
            }
            return lst;
        }

        public List<UbicacionE> ListaDepartamentos()
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_WEB_T_MAES_UBIG_ListaDepartamentos", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            List<UbicacionE> lst = new List<UbicacionE>();
            SqlDataReader dr;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                UbicacionE obj = new UbicacionE();
                while (dr.Read())
                {
                    obj = new UbicacionE();
                    obj.IDUbicacion = dr.GetInt32(dr.GetOrdinal("UBIG_chCODDEP"));
                    obj.Descripcion = dr.GetString(dr.GetOrdinal("UBIG_chDESDEP"));
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<String> DireccionJesusmaria()
        {
            SqlConnection conn = new SqlConnection("Server=192.168.106.251; DataBase=dbObslocal; User ID=sa; Pwd=omega2");
            SqlCommand cmdListar = new SqlCommand("sp_s_WEB_T_DIRE_DESC_ListaTodos", conn);
            cmdListar.CommandType = CommandType.StoredProcedure;
            SqlDataReader drListar = null;
            try
            {
                cmdListar.Connection.Open();
                drListar = cmdListar.ExecuteReader();
                List<String> lst = new List<String>();
                while (drListar.Read())
                {/*
                    DireccionE obj = new DireccionE();
                    obj.Direccion = drListar.GetString(drListar.GetOrdinal("UBI1_chDESC"));
                    obj.Via = drListar.GetInt32(drListar.GetOrdinal("VIA1_P_inID"));*/
                    lst.Add(drListar.GetString(drListar.GetOrdinal("UBI1_chDESC")));
                }
                cmdListar.Connection.Close();
                cmdListar.Dispose();
                conn.Dispose();
                return lst;
            }
            catch (Exception ex)
            {
                cmdListar.Dispose();
                conn.Dispose();
                throw ex;
            }

        }

        public List<UbicacionE> ListaUbicaciones(string Filtro)
        {
            SqlConnection conn = new SqlConnection("Server=192.168.106.251; DataBase=dbObslocal; User ID=sa; Pwd=omega2");
            SqlCommand cmdListar = new SqlCommand("sp_s_WEB_T_MAES_UBIG_ListaUBIGV2", conn);
            cmdListar.CommandType = CommandType.StoredProcedure;
            List<UbicacionE> lst = new List<UbicacionE>();
            SqlDataReader drListar = null;
            try
            {
                cmdListar.Parameters.Add(new SqlParameter("@Filtro", Filtro));
                cmdListar.Connection.Open();
                drListar = cmdListar.ExecuteReader();
                while (drListar.Read())
                {
                    var obj = new UbicacionE();
                    obj.Descripcion = drListar.GetString(0).ToLower();
                    lst.Add(obj);
                }
                cmdListar.Connection.Close();
                cmdListar.Dispose();
                conn.Dispose();               
            }
            catch(Exception ex)
            {
                cmdListar.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<String> ListarDistritos(string Filtro)
        {
            SqlConnection conn = new SqlConnection("Server=192.168.106.251; DataBase=dbObslocal; User ID=sa; Pwd=omega2");
            SqlCommand cmdListar = new SqlCommand("sp_s_WEB_T_MAES_UBIG_ListaUBIGV2", conn);
            cmdListar.CommandType = CommandType.StoredProcedure;
            List<String> lst = new List<String>();
            SqlDataReader drListar = null;
            try
            {
                cmdListar.Parameters.Add(new SqlParameter("@Filtro", Filtro));
                cmdListar.Connection.Open();
                drListar = cmdListar.ExecuteReader();
                while (drListar.Read())
                {
                    lst.Add(drListar.GetString(0).ToLower());                    
                }
                cmdListar.Connection.Close();
                cmdListar.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmdListar.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<UbicacionE> ListarVias()
        {
            List<UbicacionE> lst = new List<UbicacionE>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaObservatorio());
            SqlCommand cmd = new SqlCommand("sp_s_T_DIRE_DESC_Listar_V1", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vDIRE_P_inID", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@vEMPR_P_inID", DBNull.Value));
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var obj = new UbicacionE();
                    obj.Descripcion = dr.GetString(0);
                    obj.IDUbicacion = dr.GetInt32(1);
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                conn.Dispose();
                cmd.Dispose();
            }

            return lst;
        }
    }
}
